﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace Quản_Lý_Thư_Viện
{
    class DausachDataAccess
    {
        private DataProvider _provider = new DataProvider();
        public DausachDataAccess()
        {
            _provider.connect();
        }
        public void insertdausach(DausachInfo info)
        {
            DataTable dt = new DataTable();
            dt = LayDSDausach();
            int i = dt.Rows.Count+1;
            string imaObj = "@imageObj";
            string insertCommand = "insert into dbo.DAUSACH(MADAUSACH, MATHELOAI, TENSACH, NXB, NAMXB, TRIGIA,HINHANH,THICH,SOLANMUON) values('DS" +i+ "'," + "N'" + info.Matheloai + "'," + "N'"
                                + info.Tensach + "','" + info.Nxb + "'," + "N'"
                                + info.Namxb + "','" + info.Trigia + "',"+imaObj+",0,0)";

            _provider.ExecuteWithObject(insertCommand, imaObj, (object)info.Hinhanh);//xong!
        }
        public void updatedausach(DausachInfo info)
        {
            string imaObj = "@imageObj";
            string updateCommnad = "Update dbo.DAUSACH Set TENSACH= N'"
                            + info.Tensach + "', NXB =N'"
                            + info.Nxb + "', NAMXB = N'" + info.Namxb
                            + "', TRIGIA = '" + info.Trigia
                            +"', MATHELOAI = '"+info.Matheloai
                            +"', HINHANH = " +imaObj
                            + " Where MADAUSACH = '" + info.Madausach + "'";
            _provider.ExecuteWithObject(updateCommnad, imaObj, (object)info.Hinhanh);
            
        }
        public void deletedausach(DausachInfo info)
        {
            
            string deleteCommand = "DELETE FROM dbo.DAUSACH WHERE MADAUSACH = '" + info.Madausach + "'";
            _provider.executeNonQuery(deleteCommand);
            
        }
        public DataTable LayDSDausach()
        {
            return _provider.GetData("select * from dbo.DAUSACH order by MADAUSACH desc");
        }
        public ArrayList getdsmadausach()
        {
            string query = "SELECT * FROM dbo.DAUSACH";
            SqlDataReader reader = (SqlDataReader)_provider.executeQuery(query);

            ArrayList arr = new ArrayList();
            DausachInfo dausach;
            while (reader.Read())
            {
                dausach = new DausachInfo();
                dausach.Madausach = reader["MADAUSACH"].ToString();
                dausach.Tensach = reader["TENSACH"].ToString();
                arr.Add(dausach);
            }

            reader.Close();
            return arr;
        }
        public DataTable laytrigia()
        {
            return _provider.GetData("select TRIGIA from dbo.DAUSACH , dbo.SACH where dbo.DAUSACH.MADAUSACH=dbo.SACH.MADAUSACH and MASACH = '"+frmPhieuTraSach.masachtemp+"'");
        }
        public DataTable top4sachmuon()
        {
            return _provider.GetData("Select TOP 4 HINHANH,MADAUSACH from dbo.DAUSACH order by SOLANMUON desc");
        }
        public DataTable timmds()
        {
            return _provider.GetData("select * from DAUSACH where TENSACH like '" + frmNhapDauSach.timmds + "%'");
        }      
        public DataTable timdocgia()
        {
            return _provider.GetData("select dbo.docgia.hoten,dbo.loaithe.tenloaithe,dbo.docgia.nghethan from dbo.Docgia,dbo.loaithe where dbo.loaithe.maloaithe = dbo.docgia.maloaithe and dbo.docgia.madg = '" + frmGiaHan.madg + "'");
        }
        public DataTable top4vote()
        {
            return _provider.GetData("select TOP 4 * from DAUSACH order by THICH ");
        }
    }
}
